package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.RentalInfoDto;
import com.tianqu.tms.entity.Codes;
import com.tianqu.tms.entity.RentalLocationInfo;

public class RentalLocationInfoDaoImp extends ViewListDao implements RentalLocationInfoDao {

	protected RentalLocationInfoDaoImp() throws TmsDaoException {
		super();
	}
	protected RentalLocationInfoDaoImp(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}

	/**
	 * 租位编号存在check
	 * @return
	 * @throws TmsDaoException
	 */
	public boolean isExistLocationInfoBySerial(String serial)
	throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" SERIAL ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
			sql.append(" WHERE  SERIAL=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			return rs.next();
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 插入租位信息
	 * @return
	 * @throws TmsDaoException
	 */
	public void insertLocationInfo(RentalInfoDto dto)
	throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("    INSERT INTO     ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append("  (        ");
		sql.append("	SERIAL,");
		sql.append("	TYPE,");
		sql.append("	ACREAGE,");
		sql.append("	CARPORT,");
		sql.append("	AREA,");
		sql.append("	INSERTUSERID,");
		sql.append("	INSERTTIME,");
		sql.append("	UPDATEUSERID,");
		sql.append("	UPDATETIME,");
		sql.append("	ADDRESS,");
		sql.append("	ANNUALTRANSACTIONVOLUME");

		sql.append(")VALUES(");
		sql.append("?,?,?,?,?,?,SYSDATE(),?,SYSDATE(),?,?)");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getLocationSerial());
			ps.setInt(2, dto.getRentalType());
			ps.setInt(3, dto.getAcreage());
			ps.setInt(4, dto.getCarPort());
			ps.setInt(5, dto.getArea());
			ps.setInt(6, dto.getInsertUserId());
			ps.setInt(7, dto.getUpdateUserId());
			ps.setString(8, dto.getAddress());
			ps.setString(9, dto.getAnnualTransactionVolume());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}
	
	
	/**
	 * 插入租位操作记录
	 * @return
	 * @throws TmsDaoException
	 */
	public void insertRentalUpdateHistroy(RentalInfoDto dto)
	throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("    INSERT INTO     ");
		sql.append(TmsTableNames.TMS_RENTAL_UPDATE_HISTROY);
		sql.append("  (        ");
		sql.append("	SERIAL,");
		sql.append("	HANDLETYPE,");
		sql.append("	REMARK,");
		sql.append("	UPDATEUSERID,");
		sql.append("	UPDATETIME");

		sql.append(")VALUES(");
		sql.append("?,?,?,?,SYSDATE())");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getLocationSerial());
			ps.setInt(2, dto.getHandleType());
			ps.setString(3, dto.getRemark());
			ps.setInt(4, dto.getUpdateUserId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}
	
	/**
	 * 查询租位一览
	 * @return
	 * @throws TmsDaoException
	 */
	public List<RentalInfoDto> findRentalLocationList(String locationSerial,String[] rentalType,String[] rentalStatus,
			String acreageS,String acreageE,String carPortS,String carPortE,int rentalArea,
			String updateTimeS,String updateTimeE)throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("RENTALLOCATION.ID AS ID,");
		sql.append("RENTALLOCATION.SERIAL AS SERIAL,");
		sql.append("RENTALLOCATION.TYPE AS TYPE,");
		sql.append("RENTALLOCATION.ACREAGE AS ACREAGE,");
		sql.append("RENTALLOCATION.CARPORT AS CARPORT,");
		sql.append("RENTALLOCATION.AREA AS AREA,");
		sql.append("RENTALLOCATION.ADDRESS AS ADDRESS,");
		sql.append("CODES.ITEMVALUE AS ITEMVALUE,");
//		sql.append("CONTRACT.STATUS AS STATUS,");
		sql.append("CONTRACT3.STATUS AS STATUS,");
		sql.append("CONTRACT3.LOCATIONSERIAL AS LOCATIONSERIAL");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append(" RENTALLOCATION ");
//		sql.append(" LEFT JOIN ");
//		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
//		sql.append(" CONTRACT ");
//		sql.append(" ON RENTALLOCATION.SERIAL =  CONTRACT.LOCATIONSERIAL ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_CODES);
		sql.append(" CODES ");
		sql.append(" ON RENTALLOCATION.TYPE =  CODES.ITEMCODE ");
		sql.append(" LEFT JOIN ");
		sql.append(" (SELECT LOCATIONSERIAL,STATUS FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" STATUS IN (1,2)) AS CONTRACT3") ;	
		sql.append(" ON RENTALLOCATION.SERIAL =  CONTRACT3.LOCATIONSERIAL ");
		
		sql.append(" WHERE CODES.ITEMID =  "); 	
		sql.append(Codes.RENTAL_TYPE);
		
		if(locationSerial!=null&&!"".equals(locationSerial))
		{
			sql.append("  AND RENTALLOCATION.SERIAL = '"+locationSerial+"'");//租位编号
		}
		
		if(rentalType != null && rentalType.length > 0){
			String strRentalType = "";
			for (int i =0; i < rentalType.length; i++) {
				if(i == 0) {
					strRentalType = rentalType[i];
				} else {
					strRentalType = strRentalType + "," + rentalType[i];
				}
			}
			
			sql.append("  AND RENTALLOCATION.TYPE IN ("+strRentalType+")");//租位类型
		}
		
		if(rentalStatus != null && rentalStatus.length == 1){

			if ("1".equals(rentalStatus[0])) {
				
				sql.append("  AND RENTALLOCATION.SERIAL IN (SELECT CONTRACT2.LOCATIONSERIAL FROM ");
				sql.append(TmsTableNames.TMS_CONTRACT_INFO);
				sql.append(" CONTRACT2 ");
				sql.append(" WHERE ");
				sql.append(" CONTRACT2.STATUS IN (1,2)) ");		
			} else if ("2".equals(rentalStatus[0])){
				
				sql.append("  AND RENTALLOCATION.SERIAL NOT IN (SELECT CONTRACT2.LOCATIONSERIAL  FROM ");
				sql.append(TmsTableNames.TMS_CONTRACT_INFO);
				sql.append(" CONTRACT2 ");
				sql.append(" WHERE ");
				sql.append(" CONTRACT2.STATUS IN (1,2)) ");				
			}
		}
		
		if(!StringUtil.isNullOrBlank(acreageS) && !StringUtil.isNullOrBlank(acreageE))
		{

			sql.append("  AND RENTALLOCATION.ACREAGE >= "+acreageS);
			sql.append("  AND RENTALLOCATION.ACREAGE <="+acreageE);
		}
		
		if(!StringUtil.isNullOrBlank(acreageS) && StringUtil.isNullOrBlank(acreageE))
		{
			sql.append("  AND RENTALLOCATION.ACREAGE = "+acreageS);
		}
		
		if(StringUtil.isNullOrBlank(acreageS) && !StringUtil.isNullOrBlank(acreageE))
		{
			sql.append("  AND RENTALLOCATION.ACREAGE ="+acreageE);
		}
		
		if(!StringUtil.isNullOrBlank(carPortS) && !StringUtil.isNullOrBlank(carPortE))
		{
			sql.append("  AND RENTALLOCATION.CARPORT >= "+carPortS);
			sql.append("  AND RENTALLOCATION.CARPORT <="+carPortE);
		}
		
		if(!StringUtil.isNullOrBlank(carPortS) && StringUtil.isNullOrBlank(carPortE))
		{
			sql.append("  AND RENTALLOCATION.CARPORT = "+carPortS);
		}
		
		if(StringUtil.isNullOrBlank(carPortS) && !StringUtil.isNullOrBlank(carPortE))
		{
			sql.append("  AND RENTALLOCATION.CARPORT ="+carPortE);
		}
		
		if(rentalArea > 0)
		{
			sql.append("  AND RENTALLOCATION.AREA = "+rentalArea);//
		}
		if(!StringUtil.isNullOrBlank(updateTimeS)&& !StringUtil.isNullOrBlank(updateTimeE))
		{
			sql.append("  AND DATE_FORMAT(RENTALLOCATION.UPDATETIME,'%Y-%m-%d') >=  '"+updateTimeS+"'");//开始时间
			sql.append("  AND DATE_FORMAT(RENTALLOCATION.UPDATETIME,'%Y-%m-%d') <=  '"+updateTimeE+"'");//开始时间
		}
		
		if(!StringUtil.isNullOrBlank(updateTimeS) && StringUtil.isNullOrBlank(updateTimeE))
		{
			sql.append("  AND DATE_FORMAT(RENTALLOCATION.UPDATETIME,'%Y-%m-%d') =  '"+updateTimeS+"'");//开始时间
			
		}
		
		if(StringUtil.isNullOrBlank(updateTimeS) && !StringUtil.isNullOrBlank(updateTimeE))
		{
			sql.append("  AND DATE_FORMAT(RENTALLOCATION.UPDATETIME,'%Y-%m-%d') =  '"+updateTimeE+"'");//开始时间
			
		}
		
		
		
		sql.append(" ORDER BY RENTALLOCATION.SERIAL  ");
		if (isAsc()){
			sql.append(" ASC ");
		}else{
			sql.append(" DESC ");
		}
		
		RentalInfoDto rentalInfoDto = null;
		try {

			ps = conn.prepareStatement(getSql(sql.toString()));
			rs = ps.executeQuery();
			int i=0;
			while (rs.next()) {
				if(rs.getInt("ID")>0)
				{
					rentalInfoDto = new RentalInfoDto();
					rentalInfoDto.setId(rs.getInt("ID"));
					rentalInfoDto.setLocationSerial(rs.getString("SERIAL"));
					rentalInfoDto.setRentalType(rs.getInt("TYPE"));
					rentalInfoDto.setAcreage(rs.getInt("ACREAGE"));
					rentalInfoDto.setCarPort(rs.getInt("CARPORT"));
					rentalInfoDto.setAddress(rs.getString("ADDRESS"));
					rentalInfoDto.setArea(rs.getInt("AREA"));

					int status = rs.getInt("STATUS");
					rentalInfoDto.setStatus(status);
					if(rentalStatus != null && rentalStatus.length == 1){
						
					}
					String location = rs.getString("LOCATIONSERIAL");
					if (StringUtil.isNotBlank(location)) {
						rentalInfoDto.setStatusName("已出租");
					}else{
						rentalInfoDto.setStatusName("未出租");
					}

					rentalInfoDto.setRentalTypeName(rs.getString("ITEMVALUE"));
					if(i%2 == 0){
						rentalInfoDto.setCssFlag(1);
					}else{
						rentalInfoDto.setCssFlag(2);
					}
					int num = (i+1)+(getPageNo()-1)*20;
					rentalInfoDto.setRowNo(num);
					list.add(rentalInfoDto);
					i++;					
				}
			}
			
			return list;

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	/**
	 * 租位信息查询
	 * @return
	 * @throws TmsDaoException
	 */
	@Override
	public RentalInfoDto searchLocationDetailById(long id)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("RENTALLOCATION.ID AS ID,");
		sql.append("RENTALLOCATION.SERIAL AS SERIAL,");
		sql.append("RENTALLOCATION.TYPE AS TYPE,");
		sql.append("RENTALLOCATION.ACREAGE AS ACREAGE,");
		sql.append("RENTALLOCATION.CARPORT AS CARPORT,");
		sql.append("RENTALLOCATION.AREA AS AREA,");
		sql.append("RENTALLOCATION.INSERTTIME AS INSERTTIME,");
		sql.append("RENTALLOCATION.INSERTUSERID AS INSERTUSERID,");
		sql.append("INSERTUSER.REALNAME AS INSERTUSERNAME,");
		sql.append("RENTALLOCATION.UPDATETIME AS UPDATETIME,");
		sql.append("RENTALLOCATION.UPDATEUSERID AS UPDATEUSERID,");
		sql.append("RENTALLOCATION.ADDRESS AS ADDRESS,");
		sql.append("RENTALLOCATION.ANNUALTRANSACTIONVOLUME AS ANNUALTRANSACTIONVOLUME,");
		sql.append("UPDATEUSER.REALNAME AS UPDATEUSERNAME,");
		sql.append("CODES.ITEMVALUE AS ITEMVALUE");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append(" RENTALLOCATION ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_CODES);
		sql.append(" CODES ");
		sql.append(" ON TYPE =  ITEMCODE ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" INSERTUSER ");
		sql.append(" ON RENTALLOCATION.INSERTUSERID =  INSERTUSER.ID ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" UPDATEUSER ");
		sql.append(" ON RENTALLOCATION.UPDATEUSERID =  UPDATEUSER.ID ");
		sql.append(" WHERE ITEMID =  "); 	
		sql.append(Codes.RENTAL_TYPE);
		sql.append(" AND RENTALLOCATION.ID = ?  ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, id);
			rs = ps.executeQuery();
			RentalInfoDto rentalInfoDto = new RentalInfoDto();
	     	while (rs.next()) {
				rentalInfoDto.setId(rs.getLong("ID"));
				rentalInfoDto.setLocationSerial(rs.getString("SERIAL"));
				rentalInfoDto.setRentalType(rs.getInt("TYPE"));
				rentalInfoDto.setAcreage(rs.getInt("ACREAGE"));
				rentalInfoDto.setCarPort(rs.getInt("CARPORT"));
				rentalInfoDto.setArea(rs.getInt("AREA"));
				rentalInfoDto.setInsertUserId(rs.getInt("INSERTUSERID"));
				rentalInfoDto.setInsertUserName(rs.getString("INSERTUSERNAME"));
				rentalInfoDto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				rentalInfoDto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				rentalInfoDto.setUpdateUserName(rs.getString("UPDATEUSERNAME"));
				rentalInfoDto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				rentalInfoDto.setAddress(rs.getString("ADDRESS"));
				rentalInfoDto.setRentalTypeName(rs.getString("ITEMVALUE"));
				rentalInfoDto.setAnnualTransactionVolume(rs.getString("ANNUALTRANSACTIONVOLUME"));
			}
		
	     	return rentalInfoDto;
		}
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}		
	}
	
	/**
	 * 租位信息查询
	 * @return
	 * @throws TmsDaoException
	 */
	@Override
	public RentalInfoDto searchLocationDetailBySerial(String serial)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" TYPE,");
		sql.append(" ACREAGE,");
		sql.append(" AREA,");
		sql.append(" CARPORT ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append(" WHERE SERIAL = ? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			RentalInfoDto rentalInfoDto = new RentalInfoDto();
	     	while (rs.next()) {
				rentalInfoDto.setRentalType(rs.getInt("TYPE"));
				rentalInfoDto.setAcreage(rs.getInt("ACREAGE"));
				rentalInfoDto.setCarPort(rs.getInt("CARPORT"));
				rentalInfoDto.setArea(rs.getInt("AREA"));
			}		
	     	return rentalInfoDto;
		}
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}		
	}
	
	
	/**
	 * 租位操作记录List查询
	 * */
	public List<RentalInfoDto> searchRentalLocationUpdateHistroy(String serial,String handleName,
			String handleType,String handleDateS,String handleDateE) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 0;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" ID, ");
		sql.append(" SERIAL, ");
		sql.append(" HANDLETYPE, ");
		sql.append(" REMARK, ");
		sql.append(" UPDATEUSERID, ");
		sql.append(" UPDATETIME ");		
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_RENTAL_UPDATE_HISTROY);
		sql.append(" WHERE ");
		if(serial!=null&&!"".equals(serial))
		{
			if(flag == 0){			
				flag=1;
			}
			sql.append(" SERIAL = '"+serial+"'");//租位编号
		}
		if(handleName!=null&&!"".equals(handleName))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" UPDATEUSERID = '"+handleName+"'");//操作人姓名
		}
		if(handleType!=null&&!"".equals(handleType))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" HANDLETYPE in ("+handleType+")");//操作类型
		}
		if(!StringUtil.isNullOrBlank(handleDateS)&& !StringUtil.isNullOrBlank(handleDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(UPDATETIME,'%Y-%m-%d') >=  '"+handleDateS+"'");//开始时间
			sql.append(" AND DATE_FORMAT(UPDATETIME,'%Y-%m-%d') <=  '"+handleDateE+"'");//开始时间
		}
		
		if(!StringUtil.isNullOrBlank(handleDateS) && StringUtil.isNullOrBlank(handleDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(UPDATETIME,'%Y-%m-%d') =  '"+handleDateS+"'");//开始时间
			
		}
		
		if(StringUtil.isNullOrBlank(handleDateS) && !StringUtil.isNullOrBlank(handleDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(UPDATETIME,'%Y-%m-%d') =  '"+handleDateE+"'");//开始时间
			
		}
		
		sql.append(" ORDER BY UPDATETIME DESC");
		try {
			ps = conn.prepareStatement(getSql(sql.toString()));

			rs = ps.executeQuery();
			RentalInfoDto dto = null;
			int i=0;
	     	while (rs.next()) {
	     		dto = new RentalInfoDto();
	     		dto.setId(rs.getInt("ID"));
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setHandleType(rs.getInt("HANDLETYPE"));
	     		dto.setRemark(rs.getString("REMARK"));
	     		dto.setHandleName(StringUtil.getString(rs.getInt("UPDATEUSERID")));
	     		dto.setHandleDate(rs.getTimestamp("UPDATETIME"));
	     		if(i%2 == 0){
	     			dto.setCssFlag(1);
				}else{
					dto.setCssFlag(2);
				}
	     		int num = (i+1)+(getPageNo()-1)*20;
	     		dto.setRowNo(num);
	     		list.add(dto);
	     		i++;
			}
			return list;	
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 租位状态check
	 * @return
	 * @throws TmsDaoException
	 */
	public int statusCheckById(long id)
	throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		int status = 0;
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" STATUS ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
			sql.append(" WHERE  ID=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, id);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				// 当前租位信息的租位状态（status）为“1：已出租”；“2：未出租”
				status = rs.getInt("STATUS");
			}
			return status;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 删除租位信息
	 * @return
	 * @throws TmsDaoException
	 */
	public void deleteLocationById(long id)
	throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("  DELETE	");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
			sql.append(" WHERE  ID=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 更新租位信息
	 * @return
	 * @throws TmsDaoException
	 */
	public void updateLocationInfo(RentalLocationInfo rentalLocationInfo)
	throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		try {
			sql.append("UPDATE ");
			sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
			sql.append(" SET ");
			sql.append(" TYPE=?, ");
			sql.append(" ACREAGE=?, ");
			sql.append(" CARPORT=?, ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" UPDATEUSERID=?, ");
			sql.append(" AREA=?, ");
			sql.append(" ADDRESS=?, ");
			sql.append(" ANNUALTRANSACTIONVOLUME=? ");
			sql.append(" WHERE ");
			sql.append(" ID=? ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, rentalLocationInfo.getRentalType());
			ps.setInt(2, rentalLocationInfo.getAcreage());
			ps.setInt(3, rentalLocationInfo.getCarPort());
			ps.setInt(4, rentalLocationInfo.getUpdateUserId());
			ps.setInt(5, rentalLocationInfo.getArea());
			ps.setString(6, rentalLocationInfo.getAddress());
			ps.setString(7, rentalLocationInfo.getAnnualTransactionVolume());		
			ps.setLong(8, rentalLocationInfo.getId());		
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}
	
	/**
	 * 模糊查询租位编号列表
	 * @return
	 * @throws TmsDaoException
	 */
	public List<RentalInfoDto> findLocationSerialList(String serial)throws
	TmsDaoException{

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append(" WHERE SERIAL like '%"); 	
		sql.append(serial); 
		sql.append("%' ");
		sql.append(" ORDER BY SERIAL ASC");
		
		RentalInfoDto rentalInfoDto = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {

				rentalInfoDto = new RentalInfoDto();
				rentalInfoDto.setId(rs.getLong("ID"));
				rentalInfoDto.setLocationSerial(rs.getString("SERIAL"));

				list.add(rentalInfoDto);
			}
			
			return list;

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	/**
	 * 租位信息查询
	 * @return
	 * @throws TmsDaoException
	 */
	@Override
	public RentalInfoDto searchLocationInfoById(long id)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" TYPE,");
		sql.append(" ACREAGE,");
		sql.append(" AREA,");
		sql.append(" CARPORT ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append(" WHERE ID = ? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, id);
			rs = ps.executeQuery();
			RentalInfoDto rentalInfoDto = new RentalInfoDto();
	     	while (rs.next()) {
				rentalInfoDto.setRentalType(rs.getInt("TYPE"));
				rentalInfoDto.setAcreage(rs.getInt("ACREAGE"));
				rentalInfoDto.setArea(rs.getInt("AREA"));
				rentalInfoDto.setCarPort(rs.getInt("CARPORT"));
			}		
	     	return rentalInfoDto;
		}
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}		
	}
}